import re
from common_func_defs import *
from initdatabase import *
from timefunc import *

####################################################### 私有函数定义 ########################################################
'''—————————————————————————————方法：输入日期字符串得到所属周序号—————————————————————————————'''


def get_week(date_str, begin=5):  # begin表示一周从周几开始，默认从周五（因为我们的计算周期是上周五到这周四）

    date_str = str(date_str)

    # 将字符串转换为datetime对象
    if len(date_str) > 10:
        date_str = date_str[:10]
    date_object = datetime.strptime(date_str, '%Y-%m-%d')
    # 获取星期几
    weekday = date_object.weekday()
    # 计算偏移量
    days_offset = (weekday - (begin - 1)) % 7
    # 调整日期
    adjusted_date = date_object - timedelta(days=days_offset)
    # 获取ISO年份和ISO周数
    iso_year, iso_week, _ = adjusted_date.isocalendar()
    # 输出结果
    # print(f"The adjusted ISO year and week for {date_str} are: {iso_year}, {iso_week}")
    # 注：.isocalendar() 中星期一是一周的第一天（ISO星期一为1，ISO星期日为7）；.weekday() 中星期一是一周的第零天（星期一为0，星期日为6）
    return iso_year, iso_week


'''—————————————————————————————方法：得到对应周的起始终止日期—————————————————————————————'''


def get_week_range(year_week, begin=5):
    numbers = re.findall(r'\d+', year_week)
    year = int(numbers[0])
    week = int(numbers[1])

    # 获取指定ISO年份和ISO周数的第一天
    january_fourth = datetime(year, 1, 4)
    day_offset = (begin - 1) - january_fourth.weekday()
    first_day = january_fourth + timedelta(days=day_offset)
    week_one = first_day + timedelta(weeks=(week - 1))
    # 计算该周的起始日期和结束日期
    start_date = week_one
    end_date = week_one + timedelta(days=6)
    result = start_date.strftime('%Y-%m-%d') + '~' + end_date.strftime('%Y-%m-%d')

    return result


'''—————————————————————————————方法：淘宝客竞品推广日统计表（淘客报表加工）—————————————————————————————'''


def dtk_competing_brand_promotion_day_stat_exe(start_date, end_date, competing_brand_name=''):
    engine = create_engine(DB_CONNECT)

    df_dtk_competing_brand_promotion_day = pd.read_sql_query(
        f"SELECT * FROM dtk_competing_brand_promotion_day WHERE stat_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)

    if df_dtk_competing_brand_promotion_day.empty:
        return None
    df_dtk_competing_brand_promotion_day = df_dtk_competing_brand_promotion_day.drop(
        ['id', 'crawl_time', 'create_time'], axis=1)
    # 丢弃不需要的列变量

    # df_dtk_competing_brand_promotion_day = df_dtk_competing_brand_promotion_day.applymap(format_number) # 不用运行了，这里是转换数字为字符串

    if competing_brand_name != '':
        df_dtk_competing_brand_promotion_day = df_dtk_competing_brand_promotion_day[df_dtk_competing_brand_promotion_day['competing_brand_name'] == competing_brand_name]

    # 转中文字段名
    df_dtk_competing_brand_promotion_day = uploaded_field_corr_entozh_res(df_dtk_competing_brand_promotion_day,
                                                                          '淘宝客竞品推广日表')
    return df_dtk_competing_brand_promotion_day


def get_dtk_period_stat(start_date: str, end_date: str, whether_integrate, competing_brand_name='', unit=''):
    # start_date = '2024-04-05'
    # end_date = '2024-05-01'

    try:
        if pd.to_datetime(start_date) < pd.to_datetime('2024-04-01'):
            start_date = '2024-04-01'
        else:
            pass
    except:
        print('请输入正确的统计单位！（日/周/月/年）')
        return [False, '请输入正确的统计单位！（日/周/月/年）']


    if whether_integrate == '否':
        df_from_db = dtk_competing_brand_promotion_day_stat_exe(start_date, end_date, competing_brand_name)
    else:
        engine = create_engine(DB_CONNECT)

        if unit == '日':
            pass
        elif unit == '周':
            print('周')
            start_date, end_date = week_adjust(start_date, end_date)
        elif unit == '月':
            start_date, end_date = month_adjust(start_date, end_date)
        elif unit == '年':
            start_date, end_date = find_largest_complete_years(start_date, end_date)
        else:
            print('请输入正确的统计单位！（日/周/月/年）')
            return [False, '请输入正确的统计单位！（日/周/月/年）']

        if start_date == '':  # 筛选日期没有返回正确值
            print(f'选取时间段内无匹配到的完整"{unit}"时间段')
            return [False, f'选取时间段内无匹配到的完整"{unit}"时间段']

        df_from_db = pd.read_sql_query(
            f"SELECT * FROM dtk_competing_brand_promotion_day_stat WHERE stat_time >= '{start_date}' AND stat_time <= '{end_date}'",
            engine)  # 先读入数据。

        if df_from_db.empty:
            return [False, f'选取时间段内无查询记录']
        else:
            # 将时间戳转换为 datetime 对象
            start_date = datetime.strptime(start_date, '%Y-%m-%d')
            end_date = datetime.strptime(end_date, '%Y-%m-%d')

            # 计算时间差
            days_difference = (end_date - start_date).days
            if days_difference != len(df_from_db['stat_time'].unique()):
                print('时间段内某日值存在缺失')
                if start_date != df_from_db['stat_time'].min():
                    return [False, f'选取时间段调整时间段后，开始日期无记录']

        # 根据所需统计单位输出表格
        if unit == '日':
            pass

        elif unit == '周':
            # 添加周编号、周的日期范围
            df_from_db['stat_time'] = df_from_db['stat_time'].apply(str).apply(get_week)
            df_from_db['stat_time'] = df_from_db['stat_time'].apply(str).apply(get_week_range)
            pass

        elif unit == '月':
            # 添加月编号
            df_from_db['stat_time'] = pd.to_datetime(df_from_db['stat_time']).dt.strftime('%Y-%m')
            pass

        elif unit == '年':
            # 添加年编号
            df_from_db['stat_time'] = pd.to_datetime(df_from_db['stat_time']).dt.strftime('%Y')
            pass


        groupby_list = ['stat_time', 'competing_brand_name']
        df_from_db = df_from_db.groupby(groupby_list).agg(coupon_amount_mean=('coupon_amount_mean', 'mean'),
                                                          price_after_coupon_mean=('price_after_coupon_mean', 'mean'),
                                                          commission_rate_mean=('commission_rate_mean', 'mean'),
                                                          today_sale_count_sum=('today_sale_count_sum', 'sum'),
                                                          today_sale_amount_sum=('today_sale_amount_sum', 'sum'),
                                                          good_count=('good_count', 'sum')). \
            reset_index()

        if competing_brand_name != '':
            df_from_db = df_from_db[df_from_db['competing_brand_name'] == competing_brand_name]


        df_from_db = uploaded_field_corr_entozh_res(df_from_db, '淘宝客竞品推广日、周、月、年统计表')

    return df_from_db
